package com.F05.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.F05.actionform.F0504ActionForm;
import com.F05.actionform.F0505ActionForm;
import com.dao.DataOperations;
import com.dbtools.Dispose;
import com.exception.SelectException;
import com.util.CommonTools;

public class FCollectMoneyDAO extends DataOperations{
	Logger log = Logger.getLogger(FCollectMoneyDAO.class);
	public FCollectMoneyDAO(){
		
	}
    public FCollectMoneyDAO(Connection connection){
        super(connection);
    }
    
    public List queryList(F0504ActionForm actionForm) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();
    	sql.append(" select a.id,contract_number,invoice_number,manufacturing_order,invoice_date,a.customers_code, total_amount,");
    	sql.append(" esti_collect_date,real_collect_date ,b.customers_name ");
        sql.append(" from f_collect_money a,c_customers b ");
        sql.append(" where a.customers_code = b.customers_code ");
 
        if(!actionForm.getManufacturingOrder1().equals("")){
    		sql.append(" and a.manufacturing_order >="+actionForm.getManufacturingOrder1());
    	}
    	if(!actionForm.getManufacturingOrder2().equals("")){
    		sql.append(" and a.manufacturing_order <="+actionForm.getManufacturingOrder2());
    	}
    	if(!actionForm.getContractNum1().equals("")){
    		sql.append(" and a.contract_number >="+actionForm.getContractNum1());
    	}
    	if(!actionForm.getContractNum2().equals("")){
    		sql.append(" and a.contract_number <="+actionForm.getContractNum2());
    	}
    	if(!actionForm.getInvoiceDate1().equals("")){
    		sql.append(" and a.invoice_date >= convert(datetime,'"+actionForm.getInvoiceDate1()+"')");
    	}
    	if(!actionForm.getInvoiceDate2().equals("")){
    		sql.append(" and a.invoice_date <= convert(datetime,'"+actionForm.getInvoiceDate2()+"')");
    	}
    	if(!actionForm.getInvoiceNumber1().equals("")){
    		sql.append(" and a.invoice_number >='"+actionForm.getInvoiceNumber1()+"' ");
    	}
    	if(!actionForm.getInvoiceNumber2().equals("")){
    		sql.append(" and a.invoice_number <='"+actionForm.getInvoiceNumber2()+"' ");
    	}
    	if(!actionForm.getCustomersCode().equals("")){
    		sql.append(" and a.customers_code = '"+actionForm.getCustomersCode()+"'");
    	}
    	
    	
    	ResultSet rest = null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			Map map = new HashMap();
    			map.put("ID", CommonTools.null2String(rest.getString("id")));
    			map.put("contractNumber", CommonTools.null2String(rest.getString("contract_number")));
    			map.put("invoiceNumber", CommonTools.null2String(rest.getString("invoice_number")));
    			map.put("manufacturingOrder", CommonTools.null2String(rest.getString("manufacturing_order")));
				map.put("invoiceDate", CommonTools.getDateTime(rest.getDate("invoice_date")));
				map.put("customersCode", CommonTools.null2String(rest.getString("customers_code")));
				map.put("totalAmount", CommonTools.convertCurrencyRMB(rest.getString("total_amount")));
				map.put("estiCollectDate", CommonTools.getDateTime(rest.getDate("esti_collect_date")));
				map.put("realCollectDate", CommonTools.getDateTime(rest.getDate("real_collect_date")));
				map.put("customersName", CommonTools.null2String(rest.getString("customers_name")));
	
				list.add(map);
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return list;
    }
    
    public String selectByContractNum(String contractNumber,StringBuffer sql) throws SelectException{
    	String returnValue = "";
    	String manufacturingOrder = "";
    	String totalPrice ="";
    	String customerCode ="";
    	String customersName = "";
    	
    	sql.append(" select manufacturing_order,total_price,a.customer_code,b.customers_name");
    	sql.append(" from c_orders a ,c_customers b ");
    	sql.append(" where a.customer_code = b.customers_code ");
    	sql.append(" and a.cotract_number ='"+contractNumber+"'");
    	ResultSet rest = null;
    	try{
    		log.info("sql is "+sql);
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			manufacturingOrder = CommonTools.null2String(rest.getString("manufacturing_order"));
    			customersName = CommonTools.null2String(rest.getString("customers_name"));
    			totalPrice = CommonTools.null2String(rest.getString("total_price"));
    			customerCode = CommonTools.null2String(rest.getString("customer_code"));
    			
    			returnValue = manufacturingOrder+"*"+totalPrice+"*"+customerCode+"*"+customersName;
    		}
    		
    		
    	}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			Dispose.dispose(rest);
		}
		return returnValue;
    }
    
    public List selectById(String id) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();    	
    	
    	sql.append("SELECT a.id,contract_number,manufacturing_order,invoice_number,invoice_date,a.customers_code, total_amount,esti_collect_date,real_collect_date,b.customers_name  FROM f_collect_money a,c_customers b ");
    	sql.append(" where a.id = ").append(id);
    	sql.append(" and a.customers_code = b.customers_code ");
    	ResultSet rest = null;
    	try {
    		log.info("sql is "+sql);
    		rest = this.executeQuery(sql.toString());
			while(rest.next()){
				Map map = new HashMap();
				map.put("ID", CommonTools.null2String(rest.getString("id")));
    			map.put("contractNumber", CommonTools.null2String(rest.getString("contract_number")));
    			map.put("manufacturingOrder", CommonTools.null2String(rest.getString("manufacturing_order")));
    			map.put("invoiceNumber", CommonTools.null2String(rest.getString("invoice_number")));
				map.put("invoiceDate", CommonTools.getDateTime(rest.getDate("invoice_date")));
				map.put("customersCode", CommonTools.null2String(rest.getString("customers_code")));
				map.put("totalAmount", CommonTools.null2String(rest.getString("total_amount")));
				map.put("estiCollectDate", CommonTools.getDateTime(rest.getDate("esti_collect_date")));
				map.put("realCollectDate", CommonTools.getDateTime(rest.getDate("real_collect_date")));
				map.put("customersName", CommonTools.null2String(rest.getString("customers_name")));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			Dispose.dispose(rest);
		}
		return list;
    }
    
    public boolean update(F0504ActionForm form){
    	boolean flag =false;
    	StringBuffer sql=new StringBuffer();
		List sqlList = new ArrayList();
		
		sql.append("update f_collect_money set ");
		sql.append(" contract_number='"+form.getContractNumber()+"',");
		sql.append(" manufacturing_order='"+form.getManufacturingOrder()+"',");
		sql.append(" invoice_number='"+form.getInvoiceNumber()+"',");
		sql.append(" invoice_date=convert(datetime,'"+form.getInvoiceDate()+"'),");
		sql.append(" customers_code='"+form.getCustomersCode()+"',");
		sql.append(" total_amount='"+form.getTotalAmount()+"',");
		sql.append(" esti_collect_date=convert(datetime,'"+form.getEstiCollectDate()+"'),");
		sql.append(" real_collect_date=convert(datetime,'"+form.getRealCollectDate()+"') ");
		sql.append(" where id="+form.getId());
		sqlList.add(sql.toString());
		
		try{
			log.info("sql is "+sql.toString());
			flag=super.executeUpdate(sqlList,true); 	
			form.setErrorFlag("0");
		}catch(Exception e){
	        e.printStackTrace();
	        form.setErrorFlag("1");
		}
		return flag;
    }
    
    public boolean delete(String id,F0504ActionForm form){
		boolean flag=true;
		StringBuffer sql=new StringBuffer();
		List sqlList = new ArrayList();
		sql.append("DELETE FROM f_collect_money   ");
		sql.append("WHERE id ="+id);
		sqlList.add(sql.toString());
		try{
			log.info("sql is "+sql.toString());
			flag=super.executeUpdate(sqlList,true);
			form.setErrorFlag("0");
		}catch(Exception e){
			e.printStackTrace();
			form.setErrorFlag("1");
		}
		return flag;
	}
    
    public boolean insert(F0504ActionForm form){
		boolean flag=false;
		StringBuffer sql=new StringBuffer();
		List sqlList = new ArrayList();
		try{
			sql.append("INSERT INTO f_collect_money  ");
			sql.append("  (contract_number,manufacturing_order,invoice_number,invoice_date,customers_code, total_amount,esti_collect_date,real_collect_date ) ");
			sql.append("VALUES ('"+form.getContractNumber()+"','"+form.getManufacturingOrder()+"', '"+form.getInvoiceNumber()+"', convert(datetime,'"+form.getInvoiceDate()+"')");
			sql.append(" ,'"+form.getCustomersCode()+"','"+form.getTotalAmount()+"',convert(datetime,'"+form.getEstiCollectDate()+"'),convert(datetime,'"+form.getRealCollectDate()+"'))");
			
			sqlList.add(sql.toString());
			log.info("sql is "+sql.toString());
			flag=super.executeUpdate(sqlList,true); 
			form.setErrorFlag("0");
		}catch(SQLException ex){
			if("1".equals(String.valueOf(ex.getErrorCode()))){
				//form.setErrorFlag("主键重复,错误序号: "+String.valueOf(ex.getErrorCode()));
				form.setErrorFlag("4");
			}else{
				form.setErrorFlag("1");
			}
		}catch(Exception e){
			e.printStackTrace();
			form.setErrorFlag("1");
		}
		return flag;
	}
    
    public List initList(String sql) throws SQLException{
		
		ResultSet rest = null;
		Map map = null;
		List list = new ArrayList();
		log.info(sql);
		rest = super.executeQuery(sql);
		int i = 0;
		while(rest.next()){
			map = new HashMap();
			map.put("name", CommonTools.null2String(rest.getString("name")));
			map.put("code", CommonTools.null2String(rest.getString("code")));
			list.add(map);
			i++;
		}
		return list;
	}
}
